Namespace Datos
    Public Class clCapaDatos
        <ThreadStatic()> Friend Shared cnnShared As Data.IDbConnection
        Dim _tabla As String
        'Dim _hijo As Object
        Dim _listaObjetosBase As System.Collections.Generic.List(Of camposAtributos)
        Dim _llavePrimaria As System.Collections.Generic.List(Of camposAtributos)
        Dim _llavesForaneas As System.Collections.Generic.List(Of camposAtributos)
        'Dim _tipoDato As Type
        'Dim _objeto As Object

        Public Sub New()
            
        End Sub
#Region "Llaves y Miembros"
        Private Function getLlavePrimaria() As System.Collections.Generic.List(Of camposAtributos)
            If _llavePrimaria Is Nothing Then
                Dim cb As camposAtributos
                _llavePrimaria = New System.Collections.Generic.List(Of camposAtributos)
                For Each cb In _listaObjetosBase
                    If cb.Atributo.Tipo = enTipoCampoBase.parteLlavePrimaria Then
                        _llavePrimaria.Add(cb)
                    End If
                Next
            End If
            Return _llavePrimaria
        End Function
        Private Function getLlavesForaneas() As System.Collections.Generic.List(Of camposAtributos)
            If _llavesForaneas Is Nothing Then
                Dim cb As camposAtributos
                _llavesForaneas = New System.Collections.Generic.List(Of camposAtributos)
                For Each cb In _listaObjetosBase
                    If cb.Atributo.Tipo = enTipoCampoBase.llaveForanea Then
                        _llavesForaneas.Add(cb)
                    End If
                Next
            End If
            Return _llavesForaneas
        End Function
        Private Sub getMiembros(ByVal objeto As Object)
            If _listaObjetosBase Is Nothing Then
                Dim _tipoDato As Type = objeto.GetType
                Dim p As System.Reflection.PropertyInfo
                Dim att As definicionBaseAttribute
                _listaObjetosBase = New System.Collections.Generic.List(Of camposAtributos)
                For Each p In _tipoDato.GetProperties(Reflection.BindingFlags.Public Or Reflection.BindingFlags.FlattenHierarchy Or Reflection.BindingFlags.Instance)
                    att = DirectCast(Attribute.GetCustomAttribute(p, GetType(definicionBaseAttribute)), definicionBaseAttribute)
                    If att Is Nothing = False Then
                        'en caso sea un campo de solo lectura hay que asignar el valor directamente al field!!!!!!
                        If att.Tipo = enTipoCampoBase.campoTabla Then
                            _tabla = p.GetValue(objeto, Nothing)
                        ElseIf p.CanWrite = False Or att.fieldSoloLectura Is Nothing = False Then
                            Dim f As System.Reflection.FieldInfo
                            'si no hay filed solo de lectura se utiliza el mismo campo de la propiedad con guion bajo
                            If att.fieldSoloLectura Is Nothing Then
                                f = _tipoDato.GetField("_" & p.Name, Reflection.BindingFlags.NonPublic Or Reflection.BindingFlags.FlattenHierarchy Or Reflection.BindingFlags.Instance)
                            Else
                                f = _tipoDato.GetField(att.fieldSoloLectura, Reflection.BindingFlags.NonPublic Or Reflection.BindingFlags.FlattenHierarchy Or Reflection.BindingFlags.Instance)
                            End If
                            _listaObjetosBase.Add(New camposAtributos(p, att, f))
                        Else
                            _listaObjetosBase.Add(New camposAtributos(p, att))
                        End If
                    End If
                Next
            End If
        End Sub
#End Region
#Region "Propiedades"
        Public Shared Property Conexion() As Data.IDbConnection
            Get
                Return cnnShared
            End Get
            Set(ByVal value As Data.IDbConnection)
                cnnShared = value
            End Set
        End Property
        Public read
#End Region
        Public Function cargarDatosInterno(ByVal objeto As Object) As Boolean
            getMiembros(objeto)
            Dim cerrar As Boolean = False
            Dim cnn As Data.IDbConnection = Conexion
            If cnn.State = ConnectionState.Closed Then
                cerrar = True
                cnn.Open()
            End If
            Dim aux As Boolean = False
            'inicializa command dependiendo del tip de conexion
            Dim cmd As Data.IDbCommand
            If TypeOf cnn Is SqlClient.SqlConnection Then
                cmd = New SqlClient.SqlCommand(crearSelect, Conexion)
            ElseIf TypeOf cnn Is OracleClient.OracleConnection Then
                cmd = New OracleClient.OracleCommand(crearSelect, Conexion)
            ElseIf TypeOf cnn Is MySql.Data.MySqlClient.MySqlConnection Then
                cmd = New MySql.Data.MySqlClient.MySqlCommand(crearSelect, Conexion)

            Else
                Throw New Exception("Tipo de cliente de datos no soportado aun")
            End If

            agregarParametrosPK(cmd, objeto)

            Dim lector As Data.IDataReader = cmd.ExecuteReader
            Dim i As Integer
            If lector.Read Then
                For i = 0 To lector.FieldCount - 1
                    _listaObjetosBase(i).setData(objeto, lector(i))
                Next
                aux = True

            Else
                '_nuevo = True
                '_modificado = False
              
            End If
            lector.Close()

            If cerrar Then cnn.Close()
            Return aux
        End Function
        Public Sub guardarDatosInterno(ByVal objeto As Object, Optional ByVal nuevo As Boolean = False)
            'If _nuevo = True Or _modificado = True Then
            getMiembros(objeto)
            Dim cerrar As Boolean = False
            Dim cnn As Data.IDbConnection = Conexion
            If cnn.State = ConnectionState.Closed Then
                cerrar = True
                cnn.Open()
            End If

            'inicializa command dependiendo del tipo de conexion
            Dim cmd As Data.IDbCommand
            If TypeOf cnn Is SqlClient.SqlConnection Then
                cmd = New SqlClient.SqlCommand()
            ElseIf TypeOf cnn Is OracleClient.OracleConnection Then
                cmd = New OracleClient.OracleCommand()
            ElseIf TypeOf cnn Is MySql.Data.MySqlClient.MySqlConnection Then
                cmd = New MySql.Data.MySqlClient.MySqlCommand()
            Else
                Throw New Exception("Tipo de cliente de datos no soportado aun")
            End If
            cmd.Connection = Conexion

            If nuevo = True Then
                cmd.CommandText = crearInsert()
                agregarParametrosInserUpdt(cmd, objeto)
                Dim ident As Integer
                'modificado Rafael
                If TypeOf cmd Is SqlClient.SqlCommand Then
                    ident = cmd.ExecuteScalar
                    setIdent(ident, objeto)
                ElseIf TypeOf cnn Is OracleClient.OracleConnection Then
                    cmd.ExecuteNonQuery()
                    setIdentOra(objeto)
                ElseIf TypeOf cnn Is MySql.Data.MySqlClient.MySqlConnection Then
                    cmd.ExecuteNonQuery()
                    ident = cmd.ExecuteScalar
                    setIdentMySQL(ident, objeto)
                Else
                    Throw New Exception("Tipo de cliente de datos no soportado aun")
                End If
            Else
                cmd.CommandText = crearUpdate()
                agregarParametrosInserUpdt(cmd, objeto, True)
                agregarParametrosPK(cmd, objeto)
                cmd.ExecuteNonQuery()
            End If

            '_nuevo = False
            '_modificado = False
            ' End If

        End Sub
        Private Sub setIdentMySQL(ByVal ident As Integer, ByVal hijo As Object)
            Dim cb As camposAtributos
            For Each cb In getLlavePrimaria()
                If cb.Atributo.Tipo = enTipoCampoBase.parteLlavePrimaria And (cb.Propiedad.CanWrite = False Or cb.Atributo.fieldSoloLectura Is Nothing = False) Then
                    cb.setData(hijo, ident)
                End If
            Next

        End Sub
        Private Sub setIdentOra(ByVal hijo As Object)
            Dim aux As Decimal = 0
            Dim cb As camposAtributos
            For Each cb In getLlavePrimaria()
                If cb.Atributo.Tipo = enTipoCampoBase.parteLlavePrimaria And cb.Atributo.fieldSoloLectura Is Nothing = False AndAlso cb.Atributo.NombreSeq Is Nothing = False Then 'se utiliza el fielsololectura para saber si la propiedad es solo de lectura. No se pued utilizar el can write ya que en la clCapadatos las propiedades de campos autonumericos no pueden ser readonly.
                    Dim cmd As New OracleClient.OracleCommand("select " & cb.Atributo.NombreSeq & ".currval from dual", Conexion)
                    aux = cmd.ExecuteScalar
                    cb.setData(hijo, aux)
                End If
            Next

        End Sub
        Private Sub setIdent(ByVal ident As Integer, ByVal hijo As Object)
            Dim cb As camposAtributos
            For Each cb In getLlavePrimaria()
                If cb.Atributo.Tipo = enTipoCampoBase.parteLlavePrimaria And cb.Atributo.fieldSoloLectura Is Nothing = False Then 'se utiliza el fielsololectura para saber si la propiedad es solo de lectura. No se pued utilizar el can write ya que en la clCapadatos las propiedades de campos autonumericos no pueden ser readonly.
                    cb.setData(hijo, ident)
                End If
            Next
        End Sub
        Public Sub borrarInterno(ByVal objeto As Object)
            getMiembros(objeto)
            Dim cerrar As Boolean = False
            Dim cnn As Data.IDbConnection = Conexion
            If cnn.State = ConnectionState.Closed Then
                cerrar = True
                cnn.Open()
            End If
            Dim cmd As Data.IDbCommand
            If TypeOf cnn Is SqlClient.SqlConnection Then
                cmd = New SqlClient.SqlCommand()
            ElseIf TypeOf cnn Is OracleClient.OracleConnection Then
                cmd = New OracleClient.OracleCommand()
            ElseIf TypeOf cnn Is MySql.Data.MySqlClient.MySqlConnection Then
                cmd = New MySql.Data.MySqlClient.MySqlCommand()
            Else
                Throw New Exception("Tipo de cliente de datos no soportado aun")
            End If
            cmd.Connection = Conexion
            cmd.CommandText = "delete from " & _tabla & " where " & generarFiltroPK()
            agregarParametrosPK(cmd, objeto)
            cmd.ExecuteNonQuery()
            If cerrar Then cnn.Close()
        End Sub

#Region "Apoyo Generacion"
        Friend Sub agregarParametrosInserUpdt(ByRef cmd As Data.IDbCommand, ByVal hijo As Object, Optional ByVal esUpdate As Boolean = False)
            Dim cb As camposAtributos
            For Each cb In _listaObjetosBase
                If (cb.Propiedad.CanWrite And esUpdate = False And cb.Atributo.fieldSoloLectura Is Nothing) OrElse (cb.Propiedad.CanWrite And cb.Atributo.Tipo <> enTipoCampoBase.parteLlavePrimaria And cb.Atributo.fieldSoloLectura Is Nothing) Then 'se acaba de agregar la validacion del fiel solo lectura a la segunda parte del ORELSE
                    If TypeOf Conexion Is SqlClient.SqlConnection Then
                        CType(cmd, SqlClient.SqlCommand).Parameters.Add("@" & cb.Atributo.nombreCampoBase, clTablaGenerica.transformacionTiposSqlClient(cb.Propiedad.PropertyType)).Value = cb.getData(hijo)
                    ElseIf TypeOf Conexion Is OracleClient.OracleConnection Then
                        'Modificado Rafaella linea comentariada es la original
                        'CType(cmd, OleDb.OleDbCommand).Parameters.Add("@" & cb.Atributo.nombreCampoBase, clTablaGenerica.transformacionTiposOleDb(cb.Propiedad.PropertyType)).Value = cb.getData(hijo)
                        CType(cmd, OracleClient.OracleCommand).Parameters.Add(":" & cb.Atributo.nombreCampoBase, clTablaGenerica.transformacionTiposOracleClient(cb.Propiedad.PropertyType)).Value = cb.getData(hijo)
                    ElseIf TypeOf Conexion Is MySql.Data.MySqlClient.MySqlConnection Then
                        CType(cmd, MySql.Data.MySqlClient.MySqlCommand).Parameters.Add("?" & cb.Atributo.nombreCampoBase, clTablaGenerica.transformacionTiposMySQL(cb.Propiedad.PropertyType)).Value = cb.getData(hijo)
                    End If
                End If
            Next
        End Sub
        Friend Sub agregarParametrosPK(ByRef cmd As Data.IDbCommand, ByVal hijo As Object)
            Dim cb As camposAtributos
            For Each cb In getLlavePrimaria()
                If TypeOf Conexion Is SqlClient.SqlConnection Then
                    CType(cmd, SqlClient.SqlCommand).Parameters.Add("@" & cb.Atributo.nombreCampoBase, clTablaGenerica.transformacionTiposSqlClient(cb.Propiedad.PropertyType)).Value = cb.getData(hijo)
                ElseIf TypeOf Conexion Is OracleClient.OracleConnection Then
                    CType(cmd, OracleClient.OracleCommand).Parameters.Add(":" & cb.Atributo.nombreCampoBase, clTablaGenerica.transformacionTiposOracleClient(cb.Propiedad.PropertyType)).Value = cb.getData(hijo)
                ElseIf TypeOf Conexion Is MySql.Data.MySqlClient.MySqlConnection Then
                    CType(cmd, MySql.Data.MySqlClient.MySqlCommand).Parameters.Add("?" & cb.Atributo.nombreCampoBase, clTablaGenerica.transformacionTiposMySQL(cb.Propiedad.PropertyType)).Value = cb.getData(hijo)
                End If
            Next
        End Sub

        Private Function crearUpdate() As String
            Dim cb As camposAtributos
            Dim aux As String = "update " & _tabla & " set "
            If TypeOf Conexion Is SqlClient.SqlConnection Then
                For Each cb In _listaObjetosBase
                    If cb.Propiedad.CanWrite AndAlso cb.Atributo.fieldSoloLectura Is Nothing Then aux &= cb.Atributo.nombreCampoBase & "=@" & cb.Atributo.nombreCampoBase & ","
                Next
            ElseIf TypeOf Conexion Is OracleClient.OracleConnection Then
                For Each cb In _listaObjetosBase
                    If cb.Propiedad.CanWrite AndAlso cb.Atributo.fieldSoloLectura Is Nothing Then aux &= cb.Atributo.nombreCampoBase & "=:" & cb.Atributo.nombreCampoBase & ","
                Next
            ElseIf TypeOf Conexion Is MySql.Data.MySqlClient.MySqlConnection Then
                For Each cb In _listaObjetosBase
                    If cb.Propiedad.CanWrite AndAlso cb.Atributo.fieldSoloLectura Is Nothing Then aux &= cb.Atributo.nombreCampoBase & "=?" & cb.Atributo.nombreCampoBase & ","
                Next
            End If

            aux = aux.Substring(0, aux.Length - 1) & " where " & generarFiltroPK()
            Return aux
        End Function
        Private Function crearInsert() As String
            Dim cb As camposAtributos
            Dim aux As String = "insert into " & _tabla & "("
            For Each cb In _listaObjetosBase
                If cb.Propiedad.CanWrite AndAlso cb.Atributo.fieldSoloLectura Is Nothing Then
                    aux &= cb.Atributo.nombreCampoBase & ","
                End If
            Next
            aux = aux.Substring(0, aux.Length - 1) & ") values ("

            'insertar parametros
            '*************Modificado Rafael*****************************
            If TypeOf Conexion Is SqlClient.SqlConnection Then
                For Each cb In _listaObjetosBase
                    If cb.Propiedad.CanWrite AndAlso cb.Atributo.fieldSoloLectura Is Nothing Then
                        aux &= "@" & cb.Atributo.nombreCampoBase & ","
                    End If
                Next
            ElseIf TypeOf Conexion Is OracleClient.OracleConnection Then
                For Each cb In _listaObjetosBase
                    If cb.Propiedad.CanWrite AndAlso cb.Atributo.fieldSoloLectura Is Nothing Then
                        aux &= ":" & cb.Atributo.nombreCampoBase & ","
                    End If
                Next
            ElseIf TypeOf Conexion Is MySql.Data.MySqlClient.MySqlConnection Then
                For Each cb In _listaObjetosBase
                    If cb.Propiedad.CanWrite AndAlso cb.Atributo.fieldSoloLectura Is Nothing Then
                        aux &= "?" & cb.Atributo.nombreCampoBase & ","
                    End If
                Next
            End If

            aux = aux.Substring(0, aux.Length - 1) & ")"
            If TypeOf Conexion Is SqlClient.SqlConnection Then
                aux &= ";select isnull(scope_identity() ,0)"
            End If

            Return aux
        End Function
        Private Function crearSelect() As String
            Dim cb As camposAtributos
            Dim aux As String = "select "
            For Each cb In _listaObjetosBase
                aux &= cb.Atributo.nombreCampoBase & ", "
            Next
            aux = aux.Substring(0, aux.Length - 2)
            aux &= " from " & _tabla
            aux &= " where " & generarFiltroPK()
            Return aux
        End Function
        Private Function generarFiltroPK() As String
            Dim cb As camposAtributos
            Dim aux As String
            Dim cnn As Data.IDbConnection = Conexion
            For Each cb In getLlavePrimaria()
                If TypeOf cnn Is SqlClient.SqlConnection Then
                    aux &= String.Format(" {0}={1} and ", cb.Atributo.nombreCampoBase, "@" & cb.Atributo.nombreCampoBase)
                ElseIf TypeOf cnn Is OracleClient.OracleConnection Then
                    aux &= String.Format(" {0}={1} and ", cb.Atributo.nombreCampoBase, ":" & cb.Atributo.nombreCampoBase)
                ElseIf TypeOf cnn Is MySql.Data.MySqlClient.MySqlConnection Then
                    aux &= String.Format(" {0}={1} and ", cb.Atributo.nombreCampoBase, "?" & cb.Atributo.nombreCampoBase)
                End If
            Next
            aux = aux.Substring(0, aux.Length - 4)
            Return aux
        End Function

#End Region
    End Class
End Namespace